

'''
-- spu表
CREATE TABLE spu (
    spu_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    product_description TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);'''
class SpuTableMethods:

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_spu(self, product_name, product_description):


        sql = "INSERT INTO spu (product_name, product_description) VALUES (%s, %s)"

        self.cursor.execute(sql, (product_name, product_description))


        self.conn.commit()

    def get_spu_by_id(self, spu_id):

        sql = "SELECT * FROM spu WHERE spu_id = %s"

        self.cursor.execute(sql, (spu_id,))

        result = self.cursor.fetchone()

        return result


    def get_spu_by_name(self, product_name):

        sql = "SELECT * FROM spu WHERE product_name = %s"

        self.cursor.execute(sql, (product_name,))

        return self.cursor.fetchone()
    

    def get_all_spus(self, limit=10, page=1):

        start = (page - 1) * limit

        sql = "SELECT * FROM spu LIMIT %s OFFSET %s"

        self.cursor.execute(sql, (limit, start))

        result = self.cursor.fetchall()

        return result
    
    def update_spu(self, spu_id, product_name, product_description):

        sql = "UPDATE spu SET product_name = %s, product_description = %s WHERE spu_id = %s"

        self.cursor.execute(sql, (product_name, product_description, spu_id))

        self.conn.commit()

    def delete_spu_by_id(self, spu_id):

        sql = "DELETE FROM spu WHERE spu_id = %s"

        self.cursor.execute(sql, (spu_id,))

        self.conn.commit()

    def select_spu_by_keyword(self, keyword):
        '''select spu by keyword'''

        keyword = '%' + keyword + '%'

        sql = "SELECT * FROM spu WHERE product_name LIKE %s"

        self.cursor.execute(sql, (keyword,))

        result = self.cursor.fetchall()

        return result
    
    
    def select_spu_by_keyword_where_description(self, keyword):
        '''select spu by keyword in description'''

        keyword = '%' + keyword + '%'

        sql = "SELECT * FROM spu WHERE product_description LIKE %s"

        self.cursor.execute(sql, (keyword,))

        result = self.cursor.fetchall()

        return result

    


